﻿namespace Hidistro.SqlDal.Commodities
{
    using Hidistro.Core;
    using Hidistro.Core.Entities;
    using Hidistro.Entities.Commodities;
    using Hidistro.Entities.HOP;
    using Microsoft.Practices.EnterpriseLibrary.Data;
    using System;
    using System.Data;
    using System.Data.Common;
    using System.Text;

    public class TaobaoProductDao
    {
        private Database database = DatabaseFactory.CreateDatabase();

        public PublishToTaobaoProductInfo GetTaobaoProduct(int productId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SELECT tp.*, p.TaobaoProductId,p.ProductCode, p.Description, p.ImageUrl1, p.ImageUrl2, p.ImageUrl3, p.ImageUrl4, p.ImageUrl5,");
            builder.Append(" (SELECT MIN(SalePrice) FROM Hishop_SKUs WHERE ProductId = p.ProductId) AS SalePrice,");
            builder.Append(" (SELECT MIN(Weight) FROM Hishop_SKUs WHERE ProductId = p.ProductId) AS Weight");
            builder.AppendFormat(" FROM Hishop_Products p JOIN Taobao_Products tp ON p.ProductId = tp.ProductId WHERE p.ProductId = {0}", productId);
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand(builder.ToString());
            PublishToTaobaoProductInfo info = null;
            using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
            {
                if (!reader.Read())
                {
                    return info;
                }
                info = new PublishToTaobaoProductInfo {
                    Cid = (long) reader["Cid"]
                };
                if (reader["StuffStatus"] != DBNull.Value)
                {
                    info.StuffStatus = (string) reader["StuffStatus"];
                }
                info.ProductId = (int) reader["ProductId"];
                info.ProTitle = (string) reader["ProTitle"];
                info.Num = (long) reader["Num"];
                info.LocationState = (string) reader["LocationState"];
                info.LocationCity = (string) reader["LocationCity"];
                info.FreightPayer = (string) reader["FreightPayer"];
                if (reader["PostFee"] != DBNull.Value)
                {
                    info.PostFee = (decimal) reader["PostFee"];
                }
                if (reader["ExpressFee"] != DBNull.Value)
                {
                    info.ExpressFee = (decimal) reader["ExpressFee"];
                }
                if (reader["EMSFee"] != DBNull.Value)
                {
                    info.EMSFee = (decimal) reader["EMSFee"];
                }
                info.HasInvoice = (bool) reader["HasInvoice"];
                info.HasWarranty = (bool) reader["HasWarranty"];
                info.HasDiscount = (bool) reader["HasDiscount"];
                info.ValidThru = (long) reader["ValidThru"];
                if (reader["ListTime"] != DBNull.Value)
                {
                    info.ListTime = (DateTime) reader["ListTime"];
                }
                if (reader["PropertyAlias"] != DBNull.Value)
                {
                    info.PropertyAlias = (string) reader["PropertyAlias"];
                }
                if (reader["InputPids"] != DBNull.Value)
                {
                    info.InputPids = (string) reader["InputPids"];
                }
                if (reader["InputStr"] != DBNull.Value)
                {
                    info.InputStr = (string) reader["InputStr"];
                }
                if (reader["SkuProperties"] != DBNull.Value)
                {
                    info.SkuProperties = (string) reader["SkuProperties"];
                }
                if (reader["SkuQuantities"] != DBNull.Value)
                {
                    info.SkuQuantities = (string) reader["SkuQuantities"];
                }
                if (reader["SkuPrices"] != DBNull.Value)
                {
                    info.SkuPrices = (string) reader["SkuPrices"];
                }
                if (reader["SkuOuterIds"] != DBNull.Value)
                {
                    info.SkuOuterIds = (string) reader["SkuOuterIds"];
                }
                if (reader["FoodAttributes"] != DBNull.Value)
                {
                    info.FoodAttributes = (string) reader["FoodAttributes"];
                }
                if (reader["TaobaoProductId"] != DBNull.Value)
                {
                    info.TaobaoProductId = (long) reader["TaobaoProductId"];
                }
                if (reader["ProductCode"] != DBNull.Value)
                {
                    info.ProductCode = (string) reader["ProductCode"];
                }
                if (reader["Description"] != DBNull.Value)
                {
                    info.Description = (string) reader["Description"];
                }
                if (reader["ImageUrl1"] != DBNull.Value)
                {
                    info.ImageUrl1 = (string) reader["ImageUrl1"];
                }
                if (reader["ImageUrl2"] != DBNull.Value)
                {
                    info.ImageUrl2 = (string) reader["ImageUrl2"];
                }
                if (reader["ImageUrl3"] != DBNull.Value)
                {
                    info.ImageUrl3 = (string) reader["ImageUrl3"];
                }
                if (reader["ImageUrl4"] != DBNull.Value)
                {
                    info.ImageUrl4 = (string) reader["ImageUrl4"];
                }
                if (reader["ImageUrl5"] != DBNull.Value)
                {
                    info.ImageUrl5 = (string) reader["ImageUrl5"];
                }
                info.SalePrice = (decimal) reader["SalePrice"];
                if (reader["Weight"] != DBNull.Value)
                {
                    info.Weight = (decimal) reader["Weight"];
                }
            }
            return info;
        }

        public DataSet GetTaobaoProductDetails(int productId)
        {
            DataTable table;
            DataTable table2;
            DataTable table3;
            DataTable table4;
            DataTable table5;
            DataSet set = new DataSet();
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand("SELECT ProductId, HasSKU, ProductName, ProductCode, MarketPrice, (SELECT [Name] FROM Hishop_Categories WHERE CategoryId = p.CategoryId) AS CategoryName, (SELECT BrandName FROM Hishop_BrandCategories WHERE BrandId = p.BrandId) AS BrandName, (SELECT MIN(SalePrice) FROM Hishop_SKUs WHERE ProductId = p.ProductId) AS SalePrice, (SELECT MIN(CostPrice) FROM Hishop_SKUs WHERE ProductId = p.ProductId) AS CostPrice, (SELECT SUM(Stock) FROM Hishop_SKUs WHERE ProductId = p.ProductId) AS Stock FROM Hishop_Products p WHERE ProductId = @ProductId SELECT AttributeName, ValueStr FROM Hishop_ProductAttributes pa join Hishop_Attributes a ON pa.AttributeId = a.AttributeId JOIN Hishop_AttributeValues v ON a.AttributeId = v.AttributeId AND pa.ValueId = v.ValueId WHERE ProductId = @ProductId ORDER BY a.DisplaySequence DESC, v.DisplaySequence DESC SELECT Weight AS '重量', Stock AS '库存', CostPrice AS '成本价', SalePrice AS '一口价', SkuId AS '商家编码' FROM Hishop_SKUs s WHERE ProductId = @ProductId; SELECT SkuId AS '商家编码',AttributeName,UseAttributeImage,ValueStr,ImageUrl FROM Hishop_SKUItems s join Hishop_Attributes a on s.AttributeId = a.AttributeId join Hishop_AttributeValues av on s.ValueId = av.ValueId WHERE SkuId IN (SELECT SkuId FROM Hishop_SKUs WHERE ProductId = @ProductId) ORDER BY a.DisplaySequence DESC SELECT * FROM Taobao_Products WHERE ProductId = @ProductId");
            this.database.AddInParameter(sqlStringCommand, "ProductId", DbType.Int32, productId);
            using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
            {
                table = DataHelper.ConverDataReaderToDataTable(reader);
                reader.NextResult();
                table2 = DataHelper.ConverDataReaderToDataTable(reader);
                reader.NextResult();
                table3 = DataHelper.ConverDataReaderToDataTable(reader);
                reader.NextResult();
                table4 = DataHelper.ConverDataReaderToDataTable(reader);
                reader.NextResult();
                table5 = DataHelper.ConverDataReaderToDataTable(reader);
            }
            if (((table3 != null) && (table3.Rows.Count > 0)) && ((table4 != null) && (table4.Rows.Count > 0)))
            {
                foreach (DataRow row in table4.Rows)
                {
                    DataColumn column = new DataColumn {
                        ColumnName = (string) row["AttributeName"]
                    };
                    if (!table3.Columns.Contains(column.ColumnName))
                    {
                        table3.Columns.Add(column);
                    }
                }
                foreach (DataRow row2 in table3.Rows)
                {
                    foreach (DataRow row in table4.Rows)
                    {
                        if (string.Compare((string) row2["商家编码"], (string) row["商家编码"]) == 0)
                        {
                            row2[(string) row["AttributeName"]] = row["ValueStr"];
                        }
                    }
                }
            }
            set.Tables.Add(table);
            set.Tables.Add(table2);
            set.Tables.Add(table3);
            set.Tables.Add(table5);
            return set;
        }

        public DbQueryResult GetToTaobaoProducts(ProductQuery query)
        {
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat("SaleStatus<>{0}", 0);
            if (!string.IsNullOrEmpty(query.ProductCode))
            {
                builder.AppendFormat(" AND ProductCode = '{0}'", DataHelper.CleanSearchString(query.ProductCode));
            }
            if (!string.IsNullOrEmpty(query.Keywords))
            {
                builder.AppendFormat(" AND ProductName LIKE '%{0}%'", DataHelper.CleanSearchString(query.Keywords));
            }
            if (query.CategoryId.HasValue && (query.CategoryId.Value > 0))
            {
                builder.AppendFormat(" AND ( MainCategoryPath LIKE '{0}|%' OR ExtendCategoryPath LIKE '{0}|%')", query.MaiCategoryPath);
            }
            if (query.StartDate.HasValue)
            {
                builder.AppendFormat(" AND AddedDate >='{0}'", DataHelper.GetSafeDateTimeFormat(query.StartDate.Value));
            }
            if (query.EndDate.HasValue)
            {
                builder.AppendFormat(" AND AddedDate <='{0}'", DataHelper.GetSafeDateTimeFormat(query.EndDate.Value));
            }
            if (query.PublishStatus == PublishStatus.Already)
            {
                builder.Append(" AND TaobaoProductId <> 0");
            }
            else if (query.PublishStatus == PublishStatus.Notyet)
            {
                builder.Append(" AND TaobaoProductId = 0");
            }
            if (query.IsMakeTaobao.HasValue)
            {
                if (query.IsMakeTaobao.Value == 1)
                {
                    builder.Append(" AND ProductId IN (SELECT ProductId FROM Taobao_Products where ProductId = p.ProductId)");
                }
                else if (query.IsMakeTaobao.Value == 0)
                {
                    builder.Append(" AND ProductId NOT IN (SELECT ProductId FROM Taobao_Products where ProductId = p.ProductId)");
                }
            }
            StringBuilder builder2 = new StringBuilder();
            builder2.Append("ProductId, ProductCode, ProductName, ThumbnailUrl40, MarketPrice, SalePrice, Stock, DisplaySequence,TaobaoProductId");
            builder2.Append(",(SELECT COUNT(*) FROM Taobao_Products WHERE ProductId = p.ProductId) AS IsMakeTaobao");
            return DataHelper.PagingByRownumber(query.PageIndex, query.PageSize, query.SortBy, query.SortOrder, query.IsCount, "vw_Hishop_BrowseProductList p", "ProductId", builder.ToString(), builder2.ToString());
        }

        public bool IsExitTaobaoProduct(long taobaoProductId)
        {
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand(string.Format("SELECT COUNT(*) FROM Hishop_Products WHERE TaobaoProductId = {0}", taobaoProductId));
            return (((int) this.database.ExecuteScalar(sqlStringCommand)) > 0);
        }

        public bool UpdateTaobaoProductId(int productId, long taobaoProductId)
        {
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand(string.Format("UPDATE Hishop_Products SET TaobaoProductId = {0} WHERE ProductId = {1}", taobaoProductId, productId));
            return (this.database.ExecuteNonQuery(sqlStringCommand) > 0);
        }

        public bool UpdateToaobProduct(TaobaoProductInfo taobaoProduct)
        {
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand("DELETE FROM Taobao_Products WHERE ProductId = @ProductId;INSERT INTO Taobao_Products(Cid, StuffStatus, ProductId, ProTitle,Num, LocationState, LocationCity, FreightPayer, PostFee, ExpressFee, EMSFee, HasInvoice, HasWarranty, HasDiscount, ValidThru, ListTime, PropertyAlias,InputPids,InputStr, SkuProperties, SkuQuantities, SkuPrices, SkuOuterIds) VALUES(@Cid, @StuffStatus, @ProductId, @ProTitle,@Num, @LocationState, @LocationCity, @FreightPayer, @PostFee, @ExpressFee, @EMSFee, @HasInvoice, @HasWarranty, @HasDiscount, @ValidThru, @ListTime,@PropertyAlias,@InputPids, @InputStr, @SkuProperties, @SkuQuantities, @SkuPrices, @SkuOuterIds);");
            this.database.AddInParameter(sqlStringCommand, "ProductId", DbType.Int32, taobaoProduct.ProductId);
            this.database.AddInParameter(sqlStringCommand, "Cid", DbType.Int64, taobaoProduct.Cid);
            this.database.AddInParameter(sqlStringCommand, "StuffStatus", DbType.String, taobaoProduct.StuffStatus);
            this.database.AddInParameter(sqlStringCommand, "ProTitle", DbType.String, taobaoProduct.ProTitle);
            this.database.AddInParameter(sqlStringCommand, "Num", DbType.Int64, taobaoProduct.Num);
            this.database.AddInParameter(sqlStringCommand, "LocationState", DbType.String, taobaoProduct.LocationState);
            this.database.AddInParameter(sqlStringCommand, "LocationCity", DbType.String, taobaoProduct.LocationCity);
            this.database.AddInParameter(sqlStringCommand, "FreightPayer", DbType.String, taobaoProduct.FreightPayer);
            this.database.AddInParameter(sqlStringCommand, "PostFee", DbType.Currency, taobaoProduct.PostFee);
            this.database.AddInParameter(sqlStringCommand, "ExpressFee", DbType.Currency, taobaoProduct.ExpressFee);
            this.database.AddInParameter(sqlStringCommand, "EMSFee", DbType.Currency, taobaoProduct.EMSFee);
            this.database.AddInParameter(sqlStringCommand, "HasInvoice", DbType.Boolean, taobaoProduct.HasInvoice);
            this.database.AddInParameter(sqlStringCommand, "HasWarranty", DbType.Boolean, taobaoProduct.HasWarranty);
            this.database.AddInParameter(sqlStringCommand, "HasDiscount", DbType.Boolean, taobaoProduct.HasDiscount);
            this.database.AddInParameter(sqlStringCommand, "ValidThru", DbType.Int64, taobaoProduct.ValidThru);
            this.database.AddInParameter(sqlStringCommand, "ListTime", DbType.DateTime, taobaoProduct.ListTime);
            this.database.AddInParameter(sqlStringCommand, "PropertyAlias", DbType.String, taobaoProduct.PropertyAlias);
            this.database.AddInParameter(sqlStringCommand, "InputPids", DbType.String, taobaoProduct.InputPids);
            this.database.AddInParameter(sqlStringCommand, "InputStr", DbType.String, taobaoProduct.InputStr);
            this.database.AddInParameter(sqlStringCommand, "SkuProperties", DbType.String, taobaoProduct.SkuProperties);
            this.database.AddInParameter(sqlStringCommand, "SkuQuantities", DbType.String, taobaoProduct.SkuQuantities);
            this.database.AddInParameter(sqlStringCommand, "SkuPrices", DbType.String, taobaoProduct.SkuPrices);
            this.database.AddInParameter(sqlStringCommand, "SkuOuterIds", DbType.String, taobaoProduct.SkuOuterIds);
            return (this.database.ExecuteNonQuery(sqlStringCommand) > 0);
        }
    }
}

